package com.example.webdemo.dao.Impl;

import com.example.webdemo.bean.product;
import com.example.webdemo.dao.ProductDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

@Repository
public class ProductDaoImpl implements ProductDao {

    private final JdbcTemplate jdbcTemplate;

    // 字典缓存
    private Map<String, Map<String, String>> dictCache = new HashMap<>();

    @Autowired
    public ProductDaoImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
        loadDictCache();
    }

    // 加载字典数据到缓存
    private void loadDictCache() {
        String sql = "SELECT dict_type, dict_code, dict_name FROM base_dict WHERE status = 1 ORDER BY sort";
        jdbcTemplate.query(sql, (rs, rowNum) -> {
            String dictType = rs.getString("dict_type");
            String dictCode = rs.getString("dict_code");
            String dictName = rs.getString("dict_name");

            dictCache.computeIfAbsent(dictType, k -> new HashMap<>())
                    .put(dictCode, dictName);
            return null;
        });
    }

    // 根据字典类型和代码获取名称
    private String getDictName(String dictType, String dictCode) {
        if (dictCode == null) return null;
        Map<String, String> typeMap = dictCache.get(dictType);
        return typeMap != null ? typeMap.getOrDefault(dictCode, dictCode) : dictCode;
    }

    // 根据字典类型和名称获取代码
    private String getDictCode(String dictType, String dictName) {
        if (dictName == null) return null;
        Map<String, String> typeMap = dictCache.get(dictType);
        if (typeMap != null) {
            for (Map.Entry<String, String> entry : typeMap.entrySet()) {
                if (entry.getValue().equals(dictName)) {
                    return entry.getKey();
                }
            }
        }
        return dictName;
    }

    private class ProductRowMapper implements RowMapper<product> {
        @Override
        public product mapRow(ResultSet rs, int rowNum) throws SQLException {
            product product = new product();
            product.setProd_id(rs.getInt("prod_id"));
            product.setProd_name(rs.getString("prod_name"));

            // 转换产品类型为显示名称
            product.setProd_type(getDictName("product_type", rs.getString("prod_type")));
//            product.setOriginalProdType(rs.getString("prod_type"));

            product.setProd_price(rs.getDouble("prod_price"));
            product.setProd_cost(rs.getDouble("prod_cost"));
            product.setProd_desc(rs.getString("prod_desc"));

            // 转换产品状态为显示名称
            product.setProd_status(rs.getInt("prod_status"));
//            product.setProdStatusName(getProductStatusName(rs.getInt("prod_status")));

            product.setCreate_time(rs.getTimestamp("create_time"));
            product.setUpdate_time(rs.getTimestamp("update_time"));
            return product;
        }

        private String getProductStatusName(int status) {
            switch (status) {
                case 0: return "下架";
                case 1: return "上架";
                case 2: return "缺货";
                default: return "未知状态";
            }
        }
    }

    @Override
    public int save(product product) {
        String sql = "INSERT INTO product (prod_name, prod_type, prod_price, prod_cost, prod_desc, prod_status) " +
                "VALUES (?, ?, ?, ?, ?, ?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();

        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, product.getProd_name());
            ps.setString(2, getDictCode("product_type", product.getProd_type()));
            ps.setDouble(3, product.getProd_price());
            ps.setDouble(4, product.getProd_cost());
            ps.setString(5, product.getProd_desc());
            ps.setInt(6, product.getProd_status());
            return ps;
        }, keyHolder);

        return Objects.requireNonNull(keyHolder.getKey()).intValue();
    }

    @Override
    public int update(product product) {
        String sql = "UPDATE product SET prod_name=?, prod_type=?, prod_price=?, prod_cost=?, " +
                "prod_desc=?, prod_status=? WHERE prod_id=? ";
        return jdbcTemplate.update(sql,
                product.getProd_name(),
                getDictCode("product_type", product.getProd_type()),
                product.getProd_price(),
                product.getProd_cost(),
                product.getProd_desc(),
                product.getProd_status(),
                product.getProd_id());
    }

    @Override
    public int delete(int id) {
        String sql = "DELETE FROM product WHERE prod_id = ?";
        return jdbcTemplate.update(sql, id);
    }

    @Override
    public Optional<product> findById(int id) {
        String sql = "SELECT * FROM product WHERE prod_id = ?";
        try {
            product product = jdbcTemplate.queryForObject(sql, new ProductRowMapper(), id);
            return Optional.ofNullable(product);
        } catch (Exception e) {
            return Optional.empty();
        }
    }

    @Override
    public List<product> findAll() {
        String sql = "SELECT * FROM product ORDER BY create_time ";
        return jdbcTemplate.query(sql, new ProductRowMapper());
    }

    @Override
    public List<product> searchProducts(String name, String type, Integer status) {
        StringBuilder sql = new StringBuilder("SELECT * FROM product WHERE 1=1");
        List<Object> params = new ArrayList<>();

        if (name != null && !name.isEmpty()) {
            sql.append(" AND prod_name LIKE ?");
            params.add("%" + name + "%");
        }
        if (type != null && !type.isEmpty()) {
            sql.append(" AND prod_type = ?");
            params.add(getDictCode("product_type", type));
        }
        if (status != null) {
            sql.append(" AND prod_status = ?");
            params.add(status);
        }

        return jdbcTemplate.query(sql.toString(), params.toArray(), new ProductRowMapper());
    }

    // 获取产品类型选项
    public Map<String, String> getProductTypeOptions() {
        return dictCache.getOrDefault("product_type", new HashMap<>());
    }

    // 获取产品状态选项
    public Map<Integer, String> getProductStatusOptions() {
        Map<Integer, String> statusOptions = new HashMap<>();
        statusOptions.put(0, "下架");
        statusOptions.put(1, "上架");
        statusOptions.put(2, "缺货");
        return statusOptions;
    }
}
